{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "7_recbaserecall.ipynb",
      "version": "0.3.2",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/GavinHacker/recsys_model/blob/master/7_recbaserecall.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "metadata": {
        "id": "0yZDpzmZglow",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# 使用基于电影相似度进行推荐的方法进行召回"
      ]
    },
    {
      "metadata": {
        "id": "m9qbOs6F20ey",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### install library"
      ]
    },
    {
      "metadata": {
        "id": "CedseObJhaiv",
        "colab_type": "code",
        "outputId": "404cd2f3-1498-413d-928a-71e206be3d6d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 122
        }
      },
      "cell_type": "code",
      "source": [
        "!pip install pymysql"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Collecting pymysql\n",
            "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl (47kB)\n",
            "\u001b[K    100% |████████████████████████████████| 51kB 1.8MB/s \n",
            "\u001b[?25hInstalling collected packages: pymysql\n",
            "Successfully installed pymysql-0.9.3\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "mrXGgO-U3E2R",
        "colab_type": "code",
        "outputId": "6c91ceeb-3401-46bb-c181-db275b5da455",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 122
        }
      },
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Go to this URL in a browser: https://accounts.google.com/o/oauth2/auth?client_id=947318989803-6bn6qk8qdgf4n4g3pfee6491hc0brc4i.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&scope=email%20https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdocs.test%20https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive%20https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive.photos.readonly%20https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fpeopleapi.readonly&response_type=code\n",
            "\n",
            "Enter your authorization code:\n",
            "··········\n",
            "Mounted at /content/drive\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "x_Uk0PHO2cs9",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Functional code"
      ]
    },
    {
      "metadata": {
        "id": "UVbPjjhWhR3y",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import pymysql\n",
        "import pymysql.cursors\n",
        "from functools import reduce\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "import uuid\n",
        "import datetime\n",
        "#from pyfm import pylibfm\n",
        "from sklearn.feature_extraction import DictVectorizer\n",
        "from sklearn.metrics.pairwise import pairwise_distances\n",
        "np.set_printoptions(precision=3)\n",
        "np.set_printoptions(suppress=True)\n",
        "from sklearn.datasets import dump_svmlight_file\n",
        "from sklearn.preprocessing import OneHotEncoder\n",
        "import pickle as pkl\n",
        "from sklearn.metrics import roc_auc_score, mean_squared_error\n",
        "from sklearn.datasets import load_svmlight_file\n",
        "from sklearn.linear_model import LogisticRegression"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "0txVlPpohhRN",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "def get_connection():\n",
        "    return pymysql.connect(host='rm-2zeqqm6994abi7b6dqo.mysql.rds.aliyuncs.com',\n",
        "                               user='noone',\n",
        "                               password='Huawei12#$',\n",
        "                               db='recsys',\n",
        "                               port=3306,\n",
        "                               charset ='utf8',\n",
        "                               use_unicode=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "dDAjZUJfAa6u",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 获取最新的comment，实际recsys_core实现的时候使用mqlog消息"
      ]
    },
    {
      "metadata": {
        "id": "X5TyWGLchuGU",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "def get_comment_data():\n",
        "  df_comment_new_data = pd.read_sql_query(\"select * from comment_new where newdata = 1 \", get_connection())\n",
        "  df_comment_new_data_ldim = df_comment_new_data.loc[:,['ID','MOVIEID','USERID']]\n",
        "  return df_comment_new_data_ldim"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "wtEVbUQfCKha",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 获取基于相似度推荐的电影集合"
      ]
    },
    {
      "metadata": {
        "id": "NHYzpf5ujxxA",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "def get_ibmovie_by_movieid(movieid, connection):\n",
        "  sql = 'select DISTINCT recmovieid from ibmovie where movieid = \\'%s\\'' % movieid\n",
        "  try:\n",
        "      with connection.cursor() as cursor:\n",
        "          cout=cursor.execute(sql)\n",
        "      return cursor.fetchall()\n",
        "  except Exception as e:\n",
        "      print(e)\n",
        "      connection.close()\n",
        "  return None"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "QDD7l211CTbf",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 对recmovie表插入数据，保留原始movieid，即根据哪个电影推荐而来"
      ]
    },
    {
      "metadata": {
        "id": "p7W895yclOF9",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "def insert_or_update_recmovie(movieid, userid, srcmovieid, connection):\n",
        "  _id = uuid.uuid4()\n",
        "  time_now = datetime.datetime.now()\n",
        "  q_sql = 'select id from recmovie where userid=\\'%s\\' and movieid=\\'%s\\'' % (userid, movieid)\n",
        "  i_sql = 'insert into recmovie (id, userid, movieid, rectime, srcmovieid) values (\\'%s\\', \\'%s\\', \\'%s\\', \\'%s\\', \\'%s\\')' % (_id, userid, movieid, time_now, srcmovieid)\n",
        "  exist_list = None\n",
        "  try:\n",
        "      with connection.cursor() as cursor:\n",
        "        #print(q_sql)\n",
        "        cout=cursor.execute(q_sql)\n",
        "        exist_list = cursor.fetchall()\n",
        "      if len(exist_list) > 0:\n",
        "        with connection.cursor() as cursor:\n",
        "          for item in exist_list:\n",
        "              u_sql = 'update recmovie set rectime=\\'%s\\' where id=\\'%s\\'' % (time_now, item[0])\n",
        "              cursor.execute(u_sql)\n",
        "      else:\n",
        "        with connection.cursor() as cursor:\n",
        "              cursor.execute(i_sql)\n",
        "  except Exception as e:\n",
        "      print(e)\n",
        "      connection.close()\n",
        "  return None"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "bOVusyPJsMWT",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "* test code\n",
        "    \n",
        "    insert_or_update_recmovie('10430817','cf2349f9c01f9a5cd4050aebd30ab74f',conn)"
      ]
    },
    {
      "metadata": {
        "id": "vQ37HM0p24Sj",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "def update_comment_new_data_flag(rid, connection):\n",
        "  sql = 'update comment_new set newdata = 0 where id = \\'%s\\'' % rid\n",
        "  try:\n",
        "      with connection.cursor() as cursor:\n",
        "          cout=cursor.execute(sql)\n",
        "  except Exception as e:\n",
        "      print(e)\n",
        "      connection.close()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "-YTpgaonChhY",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 根据用户的打分进行相似电影召回，暂不限数量，具体召回数量取决于用户看过的电影的相似电影集合交集"
      ]
    },
    {
      "metadata": {
        "id": "bdENKeeliCir",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "def func_main():\n",
        "  df_comment_new_data_ldim = get_comment_data()\n",
        "  conn = get_connection()\n",
        "  for i in df_comment_new_data_ldim.index:\n",
        "    print(df_comment_new_data_ldim.iloc[i]['MOVIEID'], df_comment_new_data_ldim.iloc[i]['USERID'])\n",
        "    ibmovie_list = get_ibmovie_by_movieid(df_comment_new_data_ldim.iloc[i]['MOVIEID'], get_connection())\n",
        "    for j in ibmovie_list:\n",
        "      insert_or_update_recmovie(j[0],'cf2349f9c01f9a5cd4050aebd30ab74f', df_comment_new_data_ldim.iloc[i]['MOVIEID'], conn)\n",
        "    update_comment_new_data_flag(df_comment_new_data_ldim.iloc[i]['ID'], conn)\n",
        "    conn.commit()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "7a23-I7si4fW",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "#func_main()"
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}